package com.fs.base.excel;

import com.fs.base.utils.CommonUtils;
import com.fs.base.utils.ReflectionUtils;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import lombok.AccessLevel;
import lombok.NoArgsConstructor;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

/**
 * Excel输出工具类
 * <p>Created by Raofeicheng on 2016-11-9.</p>
 * @author Raofeicheng
 * @version 1.0
 */
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelWriterUtils {

	/**
	 * 写excel
	 * @param workbook     excel对象
	 * @param dataIterator 数据
	 * @param opts         excel配置
	 */
	public static <T> void write(Workbook workbook, Iterator<T> dataIterator, ExcelOptions opts) {
		if (null == workbook) {
			return;
		}
		Sheet sheet = workbook.createSheet("Sheet1");
		if (null == opts || CollectionUtils.isEmpty(opts.getHeaders())) {
			return;
		}
		Drawing drawing = null;
		if (opts.isWriteComment()) {
			drawing = sheet.createDrawingPatriarch();
		}
		Font font = workbook.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		CellStyle titleStyle = setAllBorder(workbook.createCellStyle()), borderStyle = setAllBorder(workbook.createCellStyle());
		titleStyle.setFont(font);
		titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleStyle.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
		titleStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

		List<CellRangeAddress> mergedAddresses = new ArrayList<CellRangeAddress>();
		List<HeaderColumn> mergedColumns = new ArrayList<HeaderColumn>();
		List<Integer> columnWidth = new ArrayList<Integer>();
		Map<Integer, Integer> columnMaxRow = new HashMap<Integer, Integer>();
		int rowIndex = 0, colIndex;
		for (List<HeaderColumn> columns : opts.getHeaders()) {
			colIndex = 0;
			for (HeaderColumn col : columns) {
				if (!col.isExport() || StringUtils.isBlank(col.getField()) && (StringUtils.isBlank(col.getTitle())
					|| StringUtils.equals(col.getTitle(), "操作"))) {
					for (CellRangeAddress address : mergedAddresses) {
						if (colIndex < address.getFirstColumn() || colIndex > address.getLastColumn()) continue;
						address.setLastColumn(address.getLastColumn() - col.getColSpan());
					}
					continue;
				}
				while (columnMaxRow.containsKey(colIndex) && rowIndex < columnMaxRow.get(colIndex)) {
					colIndex++;
				}
				if (StringUtils.isNotBlank(col.getField())) {
					opts.getRealHeaders().add(col);
				}
				if (1 < col.getColSpan() || 1 < col.getRowSpan()) {
					mergedAddresses.add(new CellRangeAddress(rowIndex, rowIndex + col.getRowSpan() - 1
						, colIndex, colIndex + col.getColSpan() - 1));
					mergedColumns.add(col);
				}
				Cell cell = writeCell(sheet, colIndex, rowIndex, col.getTitle(), titleStyle);
				if (null != drawing && StringUtils.isNotBlank(col.getComment())) {
					writeComment(drawing, cell, col.getComment());
				}
				if (col.isAutoWidth()) {
					columnWidth.add(CommonUtils.getByteLength(col.getTitle()));
				} else if (0 < col.getWidth()) {
					sheet.setColumnWidth(colIndex, Math.min(col.getWidth() * 35, 255 * 256));
				} else {
					columnWidth.add(null);
				}
				for (int n = 0; n < col.getColSpan(); n++, colIndex++) {
					if (!columnMaxRow.containsKey(colIndex)) columnMaxRow.put(colIndex, col.getRowSpan());
					else columnMaxRow.put(colIndex, col.getRowSpan() + columnMaxRow.get(colIndex));
				}
			}
			rowIndex++;
		}
		for (int i = 0; i < Math.min(mergedAddresses.size(), mergedColumns.size()); i++) {
			HeaderColumn column = mergedColumns.get(i);
			CellRangeAddress address = mergedAddresses.get(i);
			for (int row = address.getFirstRow(); row <= address.getLastRow(); row++) {
				for (int col = address.getFirstColumn(); col <= address.getLastColumn(); col++) {
					writeCell(sheet, col, row, "", titleStyle);
				}
			}
			sheet.addMergedRegion(address);
			writeCell(sheet, address.getFirstColumn(), address.getFirstRow(), column.getTitle(), titleStyle);
		}

		if (null == dataIterator) {
			return;
		}

		DataFormat format = sheet.getWorkbook().createDataFormat();
		Map<Integer, CellStyle> styleMap = new HashMap<Integer, CellStyle>();
		for (; dataIterator.hasNext(); rowIndex++) {
			Object obj = dataIterator.next(), val = null;
			if (null == obj) continue;
			colIndex = 0;
			for (HeaderColumn col : opts.getRealHeaders()) {
				val = ReflectionUtils.getFieldValue(obj, col.getField());
				String strVal = "";
				/*if (null != val && StringUtils.isNotBlank(col.getDictType())) {
					strVal = val.toString();
					if (StringUtils.isNotBlank(strVal)) {
						//val = DictUtils.getDictName(strVal, col.getDictType(), "");
					}
				}*/
				/*
				 * 字典数据转义
				 */
				if(null != val && col.getDictionary()!=null && col.getDictionary().containsKey(val) ){
					val = col.getDictionary().get(val);
				}
				
				String dataFormat = col.getDataFormat();
				CellStyle style = styleMap.get(colIndex);
				if (null == style) {
					if (val instanceof Date || StringUtils.isNotBlank(dataFormat)) {
						style = workbook.createCellStyle();
						style.cloneStyleFrom(borderStyle);
						style.setDataFormat(format.getFormat(StringUtils.isEmpty(dataFormat) ? "yyyy-MM-dd" : dataFormat));
					} else {
						style = borderStyle;
						//强制字符串显示未文本
						if (!(val instanceof Number)) {
							style.setDataFormat(format.getFormat("@"));
						}
					}
					styleMap.put(colIndex, style);
				}
				if (col.isAutoWidth() && colIndex < columnWidth.size() && null != val) {
					if (val instanceof Date) {
						strVal = style.getDataFormatString();
						if (StringUtils.isNotBlank(strVal)) {
							strVal = DateFormatUtils.format((Date) val, strVal);
						} else {
							strVal = val.toString();
						}
					} else if (val instanceof Number) {
						strVal = style.getDataFormatString();
						if (StringUtils.isNotBlank(strVal)) {
							strVal = new DecimalFormat(strVal).format(val);
						} else {
							strVal = val.toString();
						}
					} else {
						strVal = val.toString();
					}
					columnWidth.set(colIndex, Math.max(CommonUtils.getByteLength(strVal), columnWidth.get(colIndex)));
				}
				writeCell(sheet, colIndex++, rowIndex, val, style);
			}
		}
		styleMap.clear();

		colIndex = 0;
		for (HeaderColumn col : opts.getRealHeaders()) {
			if (col.isAutoWidth()) {
				if (colIndex < columnWidth.size() && null != columnWidth.get(colIndex) && 0 < columnWidth.get(colIndex)) {
					int width = columnWidth.get(colIndex) * 280;
					if (width < 255 * 256) {
						sheet.setColumnWidth(colIndex, width);
					}
				} else {
					autoSizeColumn(sheet, rowIndex, false, colIndex);
				}
			}
			colIndex++;
		}
	}

	/**
	 * 写单元格
	 * @param sheet       工作表
	 * @param columnIndex 列索引
	 * @param rowIndex    行索引
	 * @param value       单元格值
	 * @return 返回单元格对象
	 */
	public static Cell writeCell(Sheet sheet, int columnIndex, int rowIndex, Object value) {
		return writeCell(sheet, columnIndex, rowIndex, value, null);
	}

	/**
	 * 写单元格
	 * @param sheet       工作表
	 * @param columnIndex 列索引
	 * @param rowIndex    行索引
	 * @param value       单元格值
	 * @param style       单元格格式
	 * @return 返回单元格对象
	 */
	public static Cell writeCell(Sheet sheet, int columnIndex, int rowIndex, Object value, CellStyle style) {
		Cell cell = getCell(sheet, columnIndex, rowIndex);
		try {
			if (null != value && value instanceof Number) {
				cell.setCellValue(((Number) value).doubleValue());
			} else if (null != value && value instanceof Date) {
				cell.setCellValue((Date) value);
			} else {
				cell.setCellValue(null != value ? value + "" : "");
			}
			if (null != style) cell.setCellStyle(style);
		} catch (Exception e) { }
		return cell;
	}

	/**
	 * 写批注
	 * @param drawing 容器
	 * @param cell    单元格
	 * @param comment 批注
	 * @return 单元格
	 */
	public static Cell writeComment(Drawing drawing, Cell cell, String comment) {
		return writeComment(drawing, cell, comment, null);
	}

	/**
	 * 写批注
	 * @param drawing 容器
	 * @param cell    单元格
	 * @param comment 批注
	 * @param author  作者
	 * @return 单元格
	 */
	public static Cell writeComment(Drawing drawing, Cell cell, String comment, String author) {
		if (null == drawing || null == cell) return cell;
		comment = null == comment ? "" : comment;
		Comment com = null;
		if (drawing instanceof HSSFPatriarch) {
			com = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0
				, (short) cell.getColumnIndex(), cell.getRowIndex(), (short) (cell.getColumnIndex() + 3), cell.getRowIndex() + 4));
			com.setString(new HSSFRichTextString(comment));
		} else if (drawing instanceof XSSFDrawing) {
			com = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0
				, cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex() + 3, cell.getRowIndex() + 4));
			com.setString(new XSSFRichTextString(comment));
		}
		if (null == com) {
			return cell;
		}
		if (StringUtils.isNotBlank(author)) {
			com.setAuthor(author);
		}
		cell.setCellComment(com);
		return cell;
	}

	/**
	 * 获取Excel行，自动创建
	 * @param sheet  工作表
	 * @param rownum 行索引
	 * @return 行
	 */
	public static Row getRow(Sheet sheet, int rownum) {
		Row row = sheet.getRow(rownum);
		return null != row ? row : sheet.createRow(rownum);
	}

	/**
	 * 获取Excel单元格，自动创建
	 * @param sheet   工作表
	 * @param cellnum 列索引
	 * @param rownum  行索引
	 * @return 单元格
	 */
	public static Cell getCell(Sheet sheet, int cellnum, int rownum) {
		Row row = getRow(sheet, rownum);
		Cell cell = row.getCell(cellnum);
		return null != cell ? cell : row.createCell(cellnum);
	}

	/**
	 * 设置单元格格式全边框
	 * @param style 单元格格式
	 * @return 单元格格式
	 */
	public static CellStyle setAllBorder(CellStyle style) {
		return setAllBorder(style, true, true, true, true);
	}

	/**
	 * 设置单元格格式全边框
	 * @param style  单元格格式
	 * @param top    上边框
	 * @param right  右边框
	 * @param bottom 下边框
	 * @param left   左边框
	 * @return 单元格格式
	 */
	public static CellStyle setAllBorder(CellStyle style, boolean top, boolean right, boolean bottom, boolean left) {
		if (top) style.setBorderTop(CellStyle.BORDER_THIN);
		if (right) style.setBorderRight(CellStyle.BORDER_THIN);
		if (bottom) style.setBorderBottom(CellStyle.BORDER_THIN);
		if (left) style.setBorderLeft(CellStyle.BORDER_THIN);
		return style;
	}

	/**
	 * 列自动宽度，列中含有中文会计算有误
	 * @param sheet        工作表
	 * @param columnIndexs 列索引
	 */
	public static void autoSizeColumn(Sheet sheet, Integer... columnIndexs) {
		autoSizeColumn(sheet, false, columnIndexs);
	}

	/**
	 * 列自动宽度，列中含有中文会计算有误
	 * @param sheet          工作表
	 * @param useMergedCells 使用合并单元格
	 * @param columnIndexs   列索引
	 */
	public static void autoSizeColumn(Sheet sheet, boolean useMergedCells, Integer... columnIndexs) {
		autoSizeColumn(sheet, sheet.getPhysicalNumberOfRows(), useMergedCells, columnIndexs);
	}

	/**
	 * 列自动宽度，列中含有中文会计算有误
	 * @param sheet          工作表
	 * @param remaining      flush行数，SXSSFSheet工作表会存在缓存，所以需要先flush
	 * @param useMergedCells 使用合并单元格
	 * @param columnIndexs   列索引
	 */
	public static void autoSizeColumn(Sheet sheet, int remaining, boolean useMergedCells, Integer... columnIndexs) {
		if (ArrayUtils.isEmpty(columnIndexs)) return;
		try {
			for (int column : columnIndexs) sheet.autoSizeColumn(column, useMergedCells);
		} catch (Exception e) {

		}
	}

}
